﻿

/****** Object:  Stored Procedure dbo.GenListaRezultat    Script Date: 19.05.2006 3:01:29 PM ******/
CREATE PROCEDURE [dbo].[GenListaRezultat]   AS

DECLARE @dataUI as float, @dtDataUI as datetime
DECLARE @tabela varchar(100)
DECLARE @strData varchar(100)
DECLARE @fni	bit
SET @fni = 0

DECLARE @CodUnitate smallint
SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()


SELECT @strData=Valoare  FROM [ParamListe] WHERE IDLista=14 AND Cod='LDT' AND [User]=suser_sname() AND [Host]=host_name()
SET @strData = LTRIM(@strData)
SET @strData = RTRIM(@strData)

IF @strData is NULL  GOTO EROARE
IF LEN(@strData)<>0
BEGIN
	SET @dtDataUI = CAST(CAST(@strData AS float)  AS datetime)
END
SELECT @strData=Valoare  FROM [ParamListe] WHERE IDLista=14 AND Cod='FNI' AND [User]=suser_sname() AND [Host]=host_name()
IF @strData='1'
	SET @fni = 1



IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

SELECT c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit], 
	DebitPrecedent [DebPrec], CreditPrecedent [CrePrec],  
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
INTO [#balanta_t]
	FROM [Conturi]  c
LEFT OUTER JOIN 
	(SELECT  
			COALESCE(t2.ContCredit, t1.ContDebit) Cont,
			COALESCE(t1.SumaDebit , 0) DebCurent, 
			COALESCE(t2.SumaCredit , 0) CreCurent 
		FROM 
			(SELECT coalesce(SUM(dbo.IntreDatele(F.ValoareLei,NULL,@dtDataUI,D.DataDoc)),0) sumaDebit, F.ContDebit 
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContDebit<>'N/A' AND D.DataDoc <= @dtDataUI AND ((@fni=0) OR F.TipDocJust<>'IC') AND D.[CodUnitate]=@CodUnitate
	GROUP BY F.ContDebit) t1 
		FULL OUTER JOIN 
			(SELECT coalesce(SUM(dbo.IntreDatele(F.ValoareLei,NULL,@dtDataUI,D.DataDoc)),0) sumaCredit, F.ContCredit
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContCredit <>'N/A' AND D.DataDoc <= @dtDataUI AND ((@fni=0) OR F.TipDocJust<>'IC')  AND D.[CodUnitate]=@CodUnitate
			GROUP BY F.ContCredit) t2 
		ON t1.ContDebit = t2.ContCredit 
	) RUL 
ON dbo.IsContInclus(c.cont,rul.cont,c.terminal)=1
WHERE  c.[Cont]<>'N/A'  AND Categorie=0
and CHARINDEX('.',c.[Cont])=0  AND c.[CodUnitate]=@CodUnitate
and (DebCurent <> 0 OR DebitInitial <> 0 OR DebitPrecedent <> 0 OR CreCurent <> 0 OR CreditInitial <> 0 OR CreditPrecedent <> 0)
GROUP BY  c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent
ORDER BY c.Cont 
SELECT  Cont,ContParinte,Terminal,Denumire,Tip,DebInit,CreInit, DebPrec,CrePrec, DebCurent, CreCurent,
	DebPrec+DebCurent DebRul,
	CrePrec+CreCurent CreRul,
	dbo.SoldDebit(Tip, DebInit, CreInit) SoldInitDeb, 
	dbo.SoldCredit(Tip, DebInit, CreInit) SoldInitCre, 
	dbo.Sold(Tip, DebInit, CreInit) SoldInit, 
	dbo.SoldDebit(Tip, DebPrec, CrePrec) SoldPrecDeb, 
	dbo.SoldCredit(Tip, DebPrec, CrePrec) SoldPrecCre, 
	dbo.Sold(Tip, DebPrec, CrePrec) SoldPrec, 
	dbo.SoldDebit(Tip, DebCurent, CreCurent) SoldCurentDeb, 
	dbo.SoldCredit(Tip, DebCurent, CreCurent) SoldCurentCre, 
	dbo.Sold(Tip, DebCurent, CreCurent) SoldCurent,
	DebInit + DebPrec+ COALESCE(DebCurent, 0) DebTotal, CreInit + CrePrec + COALESCE(CreCurent, 0) CreTotal, 
	dbo.SoldDebit(Tip, DebInit + DebPrec +COALESCE(DebCurent, 0), CreInit + CrePrec+COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrec+ COALESCE(DebCurent, 0), CreInit + CrePrec+COALESCE(CreCurent, 0)) SoldTotalCre,
	dbo.Sold(Tip, DebInit + DebPrec+COALESCE(DebCurent, 0), CreInit + CrePrec+COALESCE(CreCurent, 0)) SoldTotal
INTO [#balanta] FROM [#balanta_t]


SELECT Tip,Cont,DebInit,CreInit, SoldInit, SoldTotalDeb, SoldTotalCre, SoldTotal,DebRul,CreRul FROM [#balanta] 
ORDER BY Cont

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea listei rezultat patrimonial',16,-1)
Gata: